package com.study.services;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.study.model.jdao.IAddresdetailDAO;
import com.study.model.jdao.ICategoryDAO;
import com.study.model.jdao.IDictionaryDAO;
import com.study.model.jdao.IUserDAO;
import com.study.model.mdao.AddressdetailMapper;
import com.study.model.mdao.CustomerMapper;
import com.study.model.mdao.DictionaryMapper;
import com.study.model.mdao.GoodDAO;
import com.study.model.mdao.UserMDAO;
import com.study.model.pojo.Customer;
import com.study.model.pojo.Dictionary;
import com.study.model.pojo.Good;

@Service
@Transactional(readOnly = true)
public class TestServiceImpl implements ITestService {
 
    @Autowired
    private CustomerMapper mapper;
    @Autowired
    IUserDAO user;
    @Autowired
    IDictionaryDAO dic;
    @Autowired
    IAddresdetailDAO address;
    
    @Autowired
    private GoodDAO gdao;
    @Autowired
    ICategoryDAO catedao;
    
 
 
    @Transactional(readOnly = false,rollbackFor = Exception.class)
    @Override
    public boolean batchImport(String fileName, MultipartFile file) throws Exception {
 
        boolean notNull = false;
        List<Customer> customerList = new ArrayList<Customer>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new Exception("上传文件格式不正确");
        }
        boolean isExcel2010 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2010 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2010) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null){
            notNull = true;
        }
        Customer customer;
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            customer = new Customer();
            if( row.getCell(0).getCellType() !=1){
                throw new Exception("导入失败(第"+(r+1)+"行,姓名请设为文本格式)");
            }
            String cus_name = row.getCell(0).getStringCellValue();
            if(cus_name == null || cus_name.isEmpty()){
                throw new Exception("导入失败(第"+(r+1)+"行,姓名未填写)");
            }
            if(row.getCell(1)!=null){
            	row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                 customer.setDictionary1(dic.findByDicId(Integer.parseInt(row.getCell(1).getStringCellValue())));
            	}
            if(row.getCell(2)!=null){
            	row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                 customer.setUser(user.findByUseId(Integer.parseInt(row.getCell(2).getStringCellValue())));
            	}
            if(row.getCell(3)!=null){
            	row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
            	customer.setDictionary2(dic.findByDicId(Integer.parseInt(row.getCell(3).getStringCellValue())));
            	}
            String cus_company = row.getCell(4).getStringCellValue();
            if(cus_company == null || cus_company.isEmpty()){
                throw new Exception("导入失败(第"+(r+1)+"行,公司名称未填写)");
            }
            if(row.getCell(5)!=null){
            	row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
            	customer.setAddresdetail(address.findByAdId(Integer.parseInt(row.getCell(5).getStringCellValue())));
            	}
            if(row.getCell(6)!=null){
            	row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
            	customer.setCusShow(Integer.parseInt(row.getCell(6).getStringCellValue()));
            	}
            customer.setCusName(cus_name);
            customer.setCusCompany(cus_company);
            customerList.add(customer);
        }
        for (Customer customerResord : customerList) {
            String name = customerResord.getCusName();
            int cnt = mapper.selectByName1(name);
            if (cnt == 0) {
            	mapper.addCustomer(customerResord);
                System.out.println(" 插入 "+customerResord);
            } else {
                mapper.updateCustomerByName(customerResord);
                System.out.println(" 更新 "+customerResord);
            }
        }
        return notNull;
    }
    
    /**
     * 产品导入
     */
    @Transactional(readOnly = false,rollbackFor = Exception.class)
    @Override
    public boolean GoodsImport(String fileName, MultipartFile file) throws Exception {
 
        boolean notNull = false;
        List<Good> goodList = new ArrayList<Good>();
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new Exception("上传文件格式不正确");
        }
        boolean isExcel2010 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2010 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2010) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        if(sheet!=null){
            notNull = true;
        }
        Good good;
        for (int r = 1; r <= sheet.getLastRowNum(); r++) {
            Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            good = new Good();
            if( row.getCell(0).getCellType() !=1){
                throw new Exception("导入失败(第"+(r+1)+"行,姓名请设为文本格式)");
            }
            if(row.getCell(0)!=null){
            	row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
            	good.setGooName(row.getCell(0).getStringCellValue());
            	}
            if(row.getCell(1)!=null){
            	row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
            	good.setCategory(catedao.findByCatId(Integer.parseInt(row.getCell(1).getStringCellValue())));
            	}
            if(row.getCell(2)!=null){
            	row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
            	good.setGooSpec(Integer.parseInt(row.getCell(2).getStringCellValue()));
            	}
            
            if(row.getCell(3)!=null){
            	row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
            	good.setGooPrice(new BigDecimal(row.getCell(3).getStringCellValue()));
            	}
            if(row.getCell(4)!=null){
            	row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
            	good.setDdtime(Integer.parseInt(row.getCell(4).getStringCellValue()));
            	}
            Dictionary dictionary2 = dic.findByDicId(506);
            Dictionary dictionary1 = dic.findByDicId(502);
            good.setDictionary1(dictionary1);
            good.setDictionary2(dictionary2);
            good.setGdel(1);
            good.setGooImg("/gimg/nullimg.png");
            goodList.add(good);
        }
        for (Good goodResord : goodList) {
            String name = goodResord.getGooName();
            int cnt = gdao.selectByName1(name);
            if (cnt == 0) {
            	gdao.addGoodByExcel(goodResord);
                System.out.println(" 插入 "+goodResord);
            } else {
            	gdao.updateGoodByName(goodResord);
                System.out.println(" 更新 "+goodResord);
            }
        }
        return notNull;
    }


}
